package approve.dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import approve.model.ListApprove;
import models.util.MySqlDataAccessHelper;

public class ListApproveDAO {
	public static ArrayList<ListApprove> getApproverMeeting11(String idapp, String db, String lang){
		ArrayList<ListApprove> lst = new ArrayList<ListApprove>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="select em.FullName, de.DepartmentName from listapprove la, approvedoc ad , employee em, department_lang de where la.IDGroupApprove=ad.IDGroupApprove and em.IDEmployee=la.IDEmployee and em.IDDepartment=de.IDDepartment and ad.IDApproveDoc='"+idapp+"' and de.LangID='"+lang+"'";
			ResultSet rs = mysql.executeQuery(sql);
			while(rs.next()){
				lst.add(new ListApprove(rs.getString("FullName"),rs.getString("DepartmentName"),"null1"));
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
	
	public static ArrayList<ListApprove> getApproversByGroupId_notallinfo(String idGroup,String database) {
		ArrayList<ListApprove> lst = new ArrayList<ListApprove>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();		
		try {
			mysql.open(database);
			String sql = "SELECT l.IDEmployee,l.Order,l.IDStatus,l.Description ,FullName, st.StatusName ,l.DateApprove FROM listapprove l, employee,`status` st WHERE l.IDEmployee = employee.IDEmployee and l.IDStatus = st.IDStatus and  l.IDGroupApprove ='"+idGroup+"' ORDER BY l.Order ASC;";
			ResultSet rs = mysql.executeQuery(sql);
			while(rs.next()){
				String sql1="SELECT l.id_employee,employee.FullName FROM list_reference l, employee WHERE l.id_manager='"+rs.getString("IDEmployee")+"' and l.id_employee=employee.IDEmployee and  l.id_group ='"+idGroup+"'";
				ResultSet rs1= mysql.executeQuery(sql1);
				int i=0;
				String s="";
				while(rs1.next()){
					if(i==0){
						s=rs1.getString("FullName");
					}else{
						s+="; "+rs1.getString("FullName");
					}
					i++;
				}
				ListApprove one= new ListApprove();
				one.setOrder(rs.getInt("Order"));
			    one.setiDStatus(rs.getInt("IDStatus"));
				one.setDescription(rs.getString("Description"));
				one.setEmname(rs.getString("FullName"));
				one.setDateApprove(rs.getTimestamp("DateApprove"));
				one.setStatusname(rs.getString("StatusName"));
				one.setListrefer(s);
				lst.add(one);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
	
	public static String getEmployeeNameByIDEmpListApp(String id,String database){
		String name = "";
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try{
			helper.open(database);
			String sql = "SELECT emp.FullName from employee emp,listapprove listapp WHERE listapp.IDEmployee = emp.IDEmployee and listapp.IDEmployee='"+id+"' GROUP BY listapp.IDEmployee";
			ResultSet rs = helper.executeQuery(sql);
			if(rs.next()){
				name = rs.getString("FullName");
			}
			rs.close();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			helper.close();
		}
		return name;
	}
	
	public static boolean updateViews(String database,String idGroup, String emp){
		String sql="update listapprove set isView = 1 where IDGroupApprove =? and IDEmployee =?";
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();	
		PreparedStatement ps = null;
		int a = 0;
		try {
			mysql.open(database);
			ps = mysql.getConnection().prepareStatement(sql);
			ps.setString(1, idGroup);
			ps.setString(2, emp);
			a = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return a>0;
	}
}